package cn.estore.dao;
//定义数据商品信息表操作

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import cn.estore.util.DBConnection;//引入DBConnection类
import cn.estore.entity.ProductEntity;//引入ProductEntity类

public class ProductDao {
    private Connection connection = null;//定义连接对象
    private PreparedStatement ps = null;//定义数据库操作的语句对象
    private DBConnection jdbc = null;//定义数据源库连接对象

    public ProductDao() {
        jdbc = new DBConnection();
        connection = jdbc.connection;//利用构造方法取得数据库连接
    }

    public List selectAllproducts() {
        List list = new ArrayList();
        ProductEntity e = null;
        try {
            ps = connection.prepareStatement("select * from tb_product order by id DESC");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                e = new ProductEntity();//为每件商品创建ProductEntity实例
                e.setId(rs.getInt(1));
                e.setCategoryMainId(rs.getInt(2));
                e.setCategoryBranchId(rs.getInt(3));
                e.setName(rs.getString(4));
                e.setProducingArea(rs.getString(5));
                e.setDescription(rs.getString(6));
                e.setCreateTime(rs.getString(7));
                e.setMarkPrice(rs.getFloat(8));
                e.setSellPrice(rs.getFloat(9));
                e.setProductAmount(rs.getInt(10));
                e.setPictrue(rs.getString(11));
                e.setDiscount(rs.getInt(12));
                list.add(e);//将ProductEntity实例加入list中
            }
        } catch (SQLException ex) {
            System.out.println("数据库访问失败");
        }
        return list;
    }

    //    查询所有商品信息
    //discount为"1"特价,"0"新品
    public List selectProductsDiscount(Integer discount) {
        List list = new ArrayList();
        try {
            ps = connection.prepareStatement("select * from tb_product where discount=? order by id DESC");
            ps.setInt(1, discount.intValue());//设置查询参数
            ResultSet rs = ps.executeQuery();
            list = Rs2List(rs);//将结果集rs转换成List对象
        } catch (SQLException ex) {
            System.out.println("数据库访问失败");
        }
        return list;
    }

    public List Rs2List(ResultSet rs) {
        List list = new ArrayList();
        ProductEntity e = null;
        try {
            while (rs.next()) {
                e = new ProductEntity();//为每件商品创建ProductEntity实例
                e.setId(rs.getInt(1));
                e.setCategoryMainId(rs.getInt(2));
                e.setCategoryBranchId(rs.getInt(3));
                e.setName(rs.getString(4));
                e.setProducingArea(rs.getString(5));
                e.setDescription(rs.getString(6));
                e.setCreateTime(rs.getString(7));
                e.setMarkPrice(rs.getFloat(8));
                e.setSellPrice(rs.getFloat(9));
                e.setProductAmount(rs.getInt(10));
                e.setPictrue(rs.getString(11));
                e.setDiscount(rs.getInt(12));
                list.add(e);//将ProductEntity实例加入list中
            }
        } catch (SQLException ex) {
            System.out.println("RS转换为List失败！");
        }
        return list;
    }

    //以商品的名称为搜索条件查询所有商品
    public List selectProductsSearch(String search) {
        List list = new ArrayList();
        ProductEntity goods = null;
        try {
            String strSql = "select * from tb_product where name like '%" + search + "%' order by id DESC";
            //拼接SQL语句已实现数据库模糊查询
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(strSql);//执行查询
            list = Rs2List(rs);
        } catch (SQLException ex) {
            System.out.println("数据库访问失败");
        }
        return list;
    }
}


